package org.xuyuji.study.spring_jdbctemplate_demo.dao.jdbc;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.xuyuji.study.spring_jdbctemplate_demo.dao.IUserDao;
import org.xuyuji.study.spring_jdbctemplate_demo.model.UserModel;

@Repository("userDao")
public class UserJdbcDaoImpl extends NamedParameterJdbcTemplate implements IUserDao {
	private static final Logger logger = Logger.getLogger(UserJdbcDaoImpl.class);
	
	@Autowired
	public UserJdbcDaoImpl(DataSource dataSource) {
		super(dataSource);
	}

	private static final String INSERT_SQL = "insert into t_user(username) values(:username)";
	private static final String DELETE_SQL = "delete from t_user where id = :id";
	private static final String UPDATE_SQL = "update t_user set username = :username, money = :money where id = :id";
	private static final String QUERY_BY_ID_SQL = "select * from t_user where id = :id";
	private static final String COUNT_ALL_SQL = "select count(*) from t_user";
	
	//调用存储过程，这里只是个例子，in_param为输入参数，out_param1和out_param2为输出参数
	private static final String PROCEDURE_SQL = "call procedure(:in_param, :out_param1, :out_param2)";

	public int save(UserModel model) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		update(INSERT_SQL, new BeanPropertySqlParameterSource(model), keyHolder);
		return keyHolder.getKey().intValue();
	}

	public void delete(int id) {
		Map<String, String> params = new HashMap<String, String>();
		params.put("id", String.valueOf(id));
		
		update(DELETE_SQL, params);
	}
	
	public void modify(UserModel model) {
		update(UPDATE_SQL, new BeanPropertySqlParameterSource(model));
	}

	public UserModel load(int id) {
		Map<String, String> params = new HashMap<String, String>();
		params.put("id", String.valueOf(id));
		
		UserModel result = null;
		try {
			result = (UserModel) queryForObject(QUERY_BY_ID_SQL, params, new BeanPropertyRowMapper<UserModel>(UserModel.class));
		} catch (EmptyResultDataAccessException e) {  
			logger.debug(e);
        }  
		
		return result;
	}
	
	public int countAll() {
		return queryForObject(COUNT_ALL_SQL, new HashMap<String, String>(), Integer.class);
	}

	/* 
	 * 批量执行插入
	 */
	public void saveBatch(Collection<UserModel> userModelList) {
		SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(userModelList.toArray());
		batchUpdate(INSERT_SQL, params);
	}

	/* 
	 * 调用存储过程
	 */
	public boolean execProcedure(final String param) {
		return getJdbcOperations().execute(PROCEDURE_SQL, new CallableStatementCallback<Boolean>() {
			public Boolean doInCallableStatement(CallableStatement callStat) throws SQLException, DataAccessException {
				callStat.setString(1, param);
				callStat.registerOutParameter(2, Types.INTEGER);
				callStat.registerOutParameter(3, Types.VARCHAR);
				callStat.execute();
				
				//假设out_param1为整数，out_param2为字符
				int out_param1 = callStat.getInt(2);
				String out_param2 = callStat.getString(3);

				return true;
			}
		});
	
	}
}
